
# To conserve interactive plots when downloading the notebook as HTML
# %%HTML
# <script src="require.js"></script>


import sys, os
from importlib import reload
import typing, requests, json, logging
reload(logging)
logging.basicConfig(stream=sys.stdout, level=logging.INFO, 
                    format='%(asctime)s [%(levelname)s] %(name)s - %(message)s',
                    datefmt='%Y-%m-%d %H:%M:%S',
                   )
from IPython.display import Markdown, display

import pandas as pd
pd.set_option('display.max_rows', None)
pd.set_option('display.max_columns', None)
pd.set_option('display.width', None)
pd.set_option('display.max_colwidth', None)

from datetime import datetime
import numpy as np
import plotly.express as px
from plotly.missing_ipywidgets import FigureWidget
import seaborn as sns

def save_html_plot(
    fig: FigureWidget, path: str
) -> None:
    fig.write_html(
        path,
        config={
            "displaylogo": False,
        },
        include_plotlyjs="cdn",
    )

OUTPUTS_PATH = "outputs/"


api_keys_dict = json.load(open("api_keys.json", "r"))
# print("api_keys.json contents:", api_keys_dict)
ETHERSCAN_API_KEY = api_keys_dict["etherscan"]
BITQUERY_API_KEY = api_keys_dict["bitquery"]

if ETHERSCAN_API_KEY == "1234abcdf" or BITQUERY_API_KEY == "1234abcdf":
    raise ValueError("Fill the api_keys.json file with your API keys before running this notebook!")

2022-11-02 13:34:52 [INFO] numexpr.utils - NumExpr defaulting to 8 threads.

{
  from: "0xEA674fdDe714fd979de3EdF0F56AA9716B898ec8",
  to: "0xac03bb73b6a9e108530aff4df5077c2b3d481e5a",
  gasLimit: "21000",
  maxFeePerGas: "300",
  maxPriorityFeePerGas: "10",
  nonce: "0",
  value: "10000000000"
}

{
    "etherscan": "1234abcdf",
    "bitquery": "1234abcdf",
    "coingecko": "1234abcdf"
}


def get_etherscan_post_request_json(url_params_dict: typing.Dict[str, typing.Any],
                                    api_key: typing.Optional[str],
                                    verbose: bool = False) -> dict:
    """
    Function to build Etherscan API URLs and performing a POST request given a set of parameters and a valid API key.

    Example of URL:

    url_params_dict = {
        "module": "account",
        "action": "txlist",
        "address": "0xc5102fE9359FD9a28f877a67E36B0F050d81a3CC",
        "startblock": 0,
        "endblock": 99999999,
        "page": 1,
        "offset": 10,
        "sort": "asc"
    }

    https://api.etherscan.io/api
           ?module=account
           &action=txlist
           &address=0xc5102fE9359FD9a28f877a67E36B0F050d81a3CC
           &startblock=0
           &endblock=99999999
           &page=1
           &offset=10
           &sort=asc
           &apikey={api_key}
    """

    url = ""
    base_url = "https://api.etherscan.io/api"

    url += base_url

    for i, item in enumerate(url_params_dict.items()):
        key = item[0]
        value = item[1]
        if i == 0:
            url += f"?{key}={value}"
        else:
            url += f"&{key}={value}"

    url += f"&apikey={api_key}"

    if verbose:
        logging.info(f"Requested url: {url}")

    request = requests.post(url)

    if request.status_code == 200:
        return request.json()
    else:
        raise Exception(
            f"Post request failed for url '{url}'. Request status code: {request.status_code}"
        )


url_params_dict = {
    "module": "account",
    "action": "txlist",
    "address": "0xAb5801a7D398351b8bE11C439e05C5B3259aeC9B", # Vitalik Buterin wallet address
    "startblock": 15828766, # Block to start searching transactions from
    "endblock": 15834718,  # Block to stop searching transactions at
    "page": 1,
    "offset": 10, # get at most 10 transactions (we expect to be just 3)
    "sort": "desc", # or asc
}


response_json = get_etherscan_post_request_json(url_params_dict, ETHERSCAN_API_KEY,True)

logging.info(f"Response JSON keys: {response_json.keys()}")
logging.info(f"status: {response_json['status']}")
logging.info(f"message: {response_json['message']}")

2022-11-02 13:34:55 [INFO] root - Requested url: https://api.etherscan.io/api?module=account&action=txlist&address=0xAb5801a7D398351b8bE11C439e05C5B3259aeC9B&startblock=15828766&endblock=15834718&page=1&offset=10&sort=desc&apikey=MCXN2GXN8WV24D8PIN8A1264VAI2AN37J7
2022-11-02 13:34:56 [INFO] root - Response JSON keys: dict_keys(['status', 'message', 'result'])
2022-11-02 13:34:56 [INFO] root - status: 1
2022-11-02 13:34:56 [INFO] root - message: OK


results = response_json["result"]
results_df = pd.json_normalize(results)
logging.info(f"Dataset Features: {list(results_df.columns)}")

results_df.head()

2022-11-02 13:34:56 [INFO] root - Dataset Features: ['blockNumber', 'timeStamp', 'hash', 'nonce', 'blockHash', 'transactionIndex', 'from', 'to', 'value', 'gas', 'gasPrice', 'isError', 'txreceipt_status', 'input', 'contractAddress', 'cumulativeGasUsed', 'gasUsed', 'confirmations', 'methodId', 'functionName']


results_df.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 3 entries, 0 to 2
Data columns (total 20 columns):
 #   Column             Non-Null Count  Dtype 
---  ------             --------------  ----- 
 0   blockNumber        3 non-null      object
 1   timeStamp          3 non-null      object
 2   hash               3 non-null      object
 3   nonce              3 non-null      object
 4   blockHash          3 non-null      object
 5   transactionIndex   3 non-null      object
 6   from               3 non-null      object
 7   to                 3 non-null      object
 8   value              3 non-null      object
 9   gas                3 non-null      object
 10  gasPrice           3 non-null      object
 11  isError            3 non-null      object
 12  txreceipt_status   3 non-null      object
 13  input              3 non-null      object
 14  contractAddress    3 non-null      object
 15  cumulativeGasUsed  3 non-null      object
 16  gasUsed            3 non-null      object
 17  confirmations      3 non-null      object
 18  methodId           3 non-null      object
 19  functionName       3 non-null      object
dtypes: object(20)
memory usage: 608.0+ bytes


relevant_integer_columns =  ["nonce" ,"transactionIndex", "value", "gas", "gasPrice", "gasUsed", "cumulativeGasUsed", "confirmations"]
results_df.loc[:,relevant_integer_columns] = results_df.loc[:,relevant_integer_columns].astype(float) 
# we convert to float since "value" is too large for int type


results_df.loc[:, "date"] = results_df["timeStamp"].astype(int).apply(datetime.utcfromtimestamp)
main_relevant_columns = ["date", "blockNumber", "hash", "from", "to", "value", "gas", "gasUsed", "gasPrice"  ]
results_df_relevant = results_df[main_relevant_columns]


results_df_relevant.head()


df_etherscan = results_df_relevant.replace("0xab5801a7d398351b8be11c439e05c5b3259aec9b", "Vitalik Buterin")

# Convert "value" from WEI to ETH and gasPrice from WEI to GWEI 
df_etherscan["value (ETH)"] = df_etherscan["value"] * 10**(-18)  
df_etherscan["gasPrice (GWEI)"] = df_etherscan["gasPrice"] * 10**(-9)  

# Txn Fee = Gas Used * Gas Price (and multiply by 10**(-9) since gasPrice is now in GWEI and we want Txn Fee in ETH)
df_etherscan["Txn Fee (ETH)"] = df_etherscan["gasUsed"] * df_etherscan["gasPrice (GWEI)"] * 10**(-9)


ETHERSCAN_TRANSACTIONS_TABLE_COLUMNS = ["hash", "date", "from", "to", "value (ETH)", "Txn Fee (ETH)", "gasPrice (GWEI)"]
df_etherscan[ETHERSCAN_TRANSACTIONS_TABLE_COLUMNS]


def get_transactions_query(address: str = "0xAb5801a7D398351b8bE11C439e05C5B3259aeC9B",
                           start_block: int = 15828766,
                           end_block: int = 15834718,
                           transactions_to_address: bool = True) -> str:
    """
    Given an <address> (contract or wallet address), a <start_block> block number and a <end_block> block
    number. 

    Return:

    - If <transactions_to_address> is True, a query to retrieve transactions that belong to blocks with 
    block number between <start_block> and <end_block>, and so that the "To" address is <address>. 

    - If <transactions_to_address> is False, the same query as when True but so that the "From" address 
    (the sender) is <address>.

    """

    address_direction_field = "txTo" if transactions_to_address else "txSender"
    date_format = "%Y-%m-%d %H:%M:%S"

    query_str = """   
        query Transactions {
          ethereum {
            transactions(
              %s: {is: "%s"}
              options: {desc: "block.timestamp.time"}
            ) {
              gasPrice
              gasValue
              hash
              to {
                address
              }
              sender {
                address
              }
              gas
              currency {
                address
                name
                symbol
              }
              amount
              block(height: {gteq: %d, lteq: %d}) {
                height
                timestamp{
                    time(format: "%s")
                  }
              }
            }
          }
        }
    """ % (address_direction_field, address, start_block, end_block, date_format)

    return query_str


transactions_to_vb_query = get_transactions_query(transactions_to_address = True)
print(transactions_to_vb_query)

   
        query Transactions {
          ethereum {
            transactions(
              txTo: {is: "0xAb5801a7D398351b8bE11C439e05C5B3259aeC9B"}
              options: {desc: "block.timestamp.time"}
            ) {
              gasPrice
              gasValue
              hash
              to {
                address
              }
              sender {
                address
              }
              gas
              currency {
                address
                name
                symbol
              }
              amount
              block(height: {gteq: 15828766, lteq: 15834718}) {
                height
                timestamp{
                    time(format: "%Y-%m-%d %H:%M:%S")
                  }
              }
            }
          }
        }


transactions_from_vb_query = get_transactions_query(transactions_to_address = False)
print(transactions_from_vb_query)

   
        query Transactions {
          ethereum {
            transactions(
              txSender: {is: "0xAb5801a7D398351b8bE11C439e05C5B3259aeC9B"}
              options: {desc: "block.timestamp.time"}
            ) {
              gasPrice
              gasValue
              hash
              to {
                address
              }
              sender {
                address
              }
              gas
              currency {
                address
                name
                symbol
              }
              amount
              block(height: {gteq: 15828766, lteq: 15834718}) {
                height
                timestamp{
                    time(format: "%Y-%m-%d %H:%M:%S")
                  }
              }
            }
          }
        }


def run_query(
    query: str, api_key: str
) -> typing.Optional[
    typing.Dict[str, typing.Any]
]:  
    """
    A simple function to use requests.post to make the API call to Bitquery API.
    """

    headers = {"X-API-KEY": api_key}
    request = requests.post(
        "https://graphql.bitquery.io/",
        json={"query": query},
        headers=headers,
    )
    if request.status_code == 200:
        return request.json()  # could be None
    else:
        return {"error": {"status_code": request.status_code}}


transactions_to_vb_json = run_query(transactions_to_vb_query, BITQUERY_API_KEY)
transactions_from_vb_json = run_query(transactions_from_vb_query, BITQUERY_API_KEY)


transactions_to_vb_json

{'data': {'ethereum': {'transactions': [{'gasPrice': 17.364768768,
     'gasValue': 0.000380496823451648,
     'hash': '0x158fbc1f6d8b44409c4adfe0b51bb255f61b36b00a32fa0d1bcd80cc239cf07e',
     'to': {'address': '0xab5801a7d398351b8be11c439e05c5b3259aec9b'},
     'sender': {'address': '0x1eaf03af983d1e20b1bab73964906c281e141d4f'},
     'gas': 21912.0,
     'currency': {'address': '-', 'name': 'Ether', 'symbol': 'ETH'},
     'amount': 0.0,
     'block': {'height': 15834718,
      'timestamp': {'time': '2022-10-26 20:44:23'}}},
    {'gasPrice': 31.584090112,
     'gasValue': 0.00085428644347904,
     'hash': '0xd0cdc5ee47422c606de85854acdd70a4178145570b907fc9e3d5aaf0af7d2245',
     'to': {'address': '0xab5801a7d398351b8be11c439e05c5b3259aec9b'},
     'sender': {'address': '0x1eaf03af983d1e20b1bab73964906c281e141d4f'},
     'gas': 27048.0,
     'currency': {'address': '-', 'name': 'Ether', 'symbol': 'ETH'},
     'amount': 0.0,
     'block': {'height': 15833688,
      'timestamp': {'time': '2022-10-26 17:17:35'}}}]}}}


transactions_to_list = transactions_to_vb_json["data"]["ethereum"]["transactions"]
transactions_from_list = transactions_from_vb_json["data"]["ethereum"]["transactions"]

transactions_to_and_from = transactions_to_list + transactions_from_list
print("List of all transactions:\n")
transactions_to_and_from

List of all transactions:

[{'gasPrice': 17.364768768,
  'gasValue': 0.000380496823451648,
  'hash': '0x158fbc1f6d8b44409c4adfe0b51bb255f61b36b00a32fa0d1bcd80cc239cf07e',
  'to': {'address': '0xab5801a7d398351b8be11c439e05c5b3259aec9b'},
  'sender': {'address': '0x1eaf03af983d1e20b1bab73964906c281e141d4f'},
  'gas': 21912.0,
  'currency': {'address': '-', 'name': 'Ether', 'symbol': 'ETH'},
  'amount': 0.0,
  'block': {'height': 15834718, 'timestamp': {'time': '2022-10-26 20:44:23'}}},
 {'gasPrice': 31.584090112,
  'gasValue': 0.00085428644347904,
  'hash': '0xd0cdc5ee47422c606de85854acdd70a4178145570b907fc9e3d5aaf0af7d2245',
  'to': {'address': '0xab5801a7d398351b8be11c439e05c5b3259aec9b'},
  'sender': {'address': '0x1eaf03af983d1e20b1bab73964906c281e141d4f'},
  'gas': 27048.0,
  'currency': {'address': '-', 'name': 'Ether', 'symbol': 'ETH'},
  'amount': 0.0,
  'block': {'height': 15833688, 'timestamp': {'time': '2022-10-26 17:17:35'}}},
 {'gasPrice': 12.383671296,
  'gasValue': 0.000260057098878976,
  'hash': '0x4655ccc7f011a1e6ce378c1e781fd1c26290f655564185f724cc125b41707d86',
  'to': {'address': '0xc79b96044906550a5652bcf20a6ea02f139b9ae5'},
  'sender': {'address': '0xab5801a7d398351b8be11c439e05c5b3259aec9b'},
  'gas': 21000.0,
  'currency': {'address': '-', 'name': 'Ether', 'symbol': 'ETH'},
  'amount': 3.923686,
  'block': {'height': 15828766, 'timestamp': {'time': '2022-10-26 00:49:23'}}}]


raw_bitquery_df = pd.json_normalize(transactions_to_and_from)
raw_bitquery_df


bitquery_table_for_etherscan_comparison = raw_bitquery_df.rename(columns = {
    "gasPrice": "gasPrice (GWEI)",
    "gasValue": "Txn Fee (ETH)",
    "amount": "value (ETH)",
    "to.address": "to",
    "sender.address": "from",
    "block.height": "blockNumber",
    "block.timestamp.time": "date"
    
})[ETHERSCAN_TRANSACTIONS_TABLE_COLUMNS].replace("0xab5801a7d398351b8be11c439e05c5b3259aec9b", "Vitalik Buterin")

# Table obtained using the Bitquery API to compare with the results obtained with Etherescan
bitquery_table_for_etherscan_comparison


# Table obtained before using Etherscan
df_etherscan[ETHERSCAN_TRANSACTIONS_TABLE_COLUMNS]


del df_etherscan, bitquery_table_for_etherscan_comparison, raw_bitquery_df


import pycoingecko 

client = pycoingecko.CoinGeckoAPI()
ALL_COINS = client.get_coins_list(include_platform=False) 
# include_platform = True gives the address of the coins in the available platforms
# Remember that Ethereum is not the only blockchain, there are many others such as Bitcoin, and some coins are not in Enthereum
# but they are in other blockchains.


logging.info(f"There are a total of {len(ALL_COINS)}coins (or tokens) listed in Coingecko.")

2022-11-02 13:34:58 [INFO] root - There are a total of 13255coins (or tokens) listed in Coingecko.


all_coins_df = pd.json_normalize(ALL_COINS)
all_coins_df.head(5)


# Subset the coins we will retrieve data about
coins_to_study_df = all_coins_df.query('id in ["cardano", "ethereum", "oasis-network", "solana", "bitcoin", "shiba-inu"]')
coins_to_study_df


coins_to_study_ids = coins_to_study_df.id.tolist()
coins_to_study_ids

['bitcoin', 'cardano', 'ethereum', 'oasis-network', 'shiba-inu', 'solana']


VSCURR = typing.Literal["usd", "eur", "gbp", "jpy"] # dollar, euro, pound and japanese yen


class HistoricalCoinPriceRetriever():
    coin_id: str
    vs_currency: VSCURR
    start_date: datetime
    end_date: datetime
    all_coins_list: typing.List[typing.Any] # client.get_coins_list(include_platform=True)

    def __init__(
        self,

        coin_id: str,
        start_date: datetime,
        end_date: datetime,
        all_coins_list: typing.List[typing.Any],
        vs_currency: VSCURR = "usd",
    ):
        super().__init__()
        self.coin_id = coin_id
        self.vs_currency = vs_currency

        self.start_date = start_date
        self.end_date = end_date

        all_coins = all_coins_list
        candidates = [v for v in all_coins if v["id"] == coin_id]

        if len(candidates) == 0:
            logging.error(f"Coin ID: {self.coin_id}")
            logging.error(f"coins list len: {len(all_coins)}")
            logging.error(
                "Coin ID is not initialized well, please review the model card parameters !!!"
            )
            raise ValueError("Invalid Coin ID")
        elif len(candidates) > 1:
            logging.warning(
                f"There are {len(candidates)} coins with same id {self.coin_id}: {candidates}"
            )

        if self.end_date.timestamp() < self.start_date.timestamp():
            logging.error(f"end_date must be greater than start_date")
            logging.error(
                f"end_date: {datetime.datetime.fromtimestamp(self.end_date.timestamp,pytz.utc).isoformat()}"
            )
            logging.error(
                f"start_date: {datetime.datetime.fromtimestamp(self.start_date.timestamp,pytz.utc).isoformat()}"
            )
            raise ValueError("end_date must be greater than start_date")

    def transform(
        self
    ) -> pd.DataFrame:
        start_date = str(int(self.start_date.timestamp()))
        end_date = str(int(self.end_date.timestamp()))

        client = pycoingecko.CoinGeckoAPI()
        results_data = client.get_coin_market_chart_range_by_id(
            self.coin_id,
            self.vs_currency,
            start_date,
            end_date,  # prices, market_cap, total_volumes
        )
        prices_df = pd.DataFrame(
            results_data["prices"], columns=[
                "datetime", f"price_in_{self.vs_currency}"]
        )
        market_cap_df = pd.DataFrame(
            results_data["market_caps"],
            columns=["datetime1", f"market_cap_in_{self.vs_currency}"],
        )
        volumes_df = pd.DataFrame(
            results_data["total_volumes"],
            columns=["datetime2", f"24h_volume_in_{self.vs_currency}"],
        )
        stats_df = pd.concat([prices_df, market_cap_df, volumes_df], axis=1)
        stats_df = stats_df[
            [
                "datetime",
                f"price_in_{self.vs_currency}",
                f"market_cap_in_{self.vs_currency}",
                f"24h_volume_in_{self.vs_currency}",
            ]
        ]

        stats_df["datetime"] = pd.to_datetime(stats_df["datetime"], unit="ms")
        stats_df["coin_id"] = self.coin_id
        
        return stats_df


# You can change the values of the three variables below if you want to experiment with different values.
START_DATE_STR = "2021-01-01"
END_DATE_STR = "2022-10-01"
VS_CURRENCY = "usd"

# Do not change code below
START_DATETIME = datetime.strptime(START_DATE_STR, '%Y-%m-%d')
END_DATETIME = datetime.strptime(END_DATE_STR, '%Y-%m-%d')

historical_coin_info_df = None
for i, coin_id in enumerate(coins_to_study_ids):
    logging.info(f"Retrieving historical coin info for coin with id '{coin_id}'...")
    retriever = HistoricalCoinPriceRetriever(coin_id, START_DATETIME, END_DATETIME, ALL_COINS, VS_CURRENCY)
    if i == 0:
        historical_coin_info_df = retriever.transform()
    else:
        historical_coin_info_df_i = retriever.transform()
        historical_coin_info_df = pd.concat([historical_coin_info_df, historical_coin_info_df_i] , axis = 0)

2022-11-02 13:34:59 [INFO] root - Retrieving historical coin info for coin with id 'bitcoin'...
2022-11-02 13:34:59 [INFO] root - Retrieving historical coin info for coin with id 'cardano'...
2022-11-02 13:34:59 [INFO] root - Retrieving historical coin info for coin with id 'ethereum'...
2022-11-02 13:34:59 [INFO] root - Retrieving historical coin info for coin with id 'oasis-network'...
2022-11-02 13:35:00 [INFO] root - Retrieving historical coin info for coin with id 'shiba-inu'...
2022-11-02 13:35:00 [INFO] root - Retrieving historical coin info for coin with id 'solana'...


historical_coin_info_df.head()


fig = px.line(historical_coin_info_df, x="datetime", y=f"price_in_{VS_CURRENCY}", color = "coin_id", 
              title = f"Coin prices in '{VS_CURRENCY}' from {START_DATE_STR} to {END_DATE_STR}"
             )
fig.show()
# save_html_plot(fig, os.path.join(OUTPUTS_PATH, f"coin_prices_in_{VS_CURRENCY}_from_{START_DATE_STR}_to{END_DATE_STR}.html"))


fig = px.line(historical_coin_info_df, x="datetime", y=f"market_cap_in_{VS_CURRENCY}", color = "coin_id", 
              title = f"Market cap in '{VS_CURRENCY}' from {START_DATE_STR} to {END_DATE_STR}"
             )
fig.show()
# save_html_plot(fig, os.path.join(OUTPUTS_PATH, f"coin_marketcaps_in_{VS_CURRENCY}_from_{START_DATE_STR}_to{END_DATE_STR}.html"))


fig = px.line(historical_coin_info_df, x="datetime", y=f"24h_volume_in_{VS_CURRENCY}", color = "coin_id", 
              title = f"24h Volume in '{VS_CURRENCY}' from {START_DATE_STR} to {END_DATE_STR}"
             )
fig.show()
# save_html_plot(fig, os.path.join(OUTPUTS_PATH, f"coin_24hvolumes_in_{VS_CURRENCY}_from_{START_DATE_STR}_to{END_DATE_STR}.html"))


USECASE_PATH = "src/usecases/DEX_trades"
from src.usecases.DEX_trades.dex_trades_retriever import DEXTradesRetriever

display(Markdown(open(os.path.join(USECASE_PATH, "README.md"), encoding='utf-8').read()))


# You can change the values of the three variables below if you want to experiment with different values.
START_DATE_STR = "2022-10-31"
END_DATE_STR = "2022-11-01"

# Do not change code below
START_DATETIME = datetime.strptime(START_DATE_STR, '%Y-%m-%d')
END_DATETIME = datetime.strptime(END_DATE_STR, '%Y-%m-%d')

trades_retriever = DEXTradesRetriever(api_key=BITQUERY_API_KEY, start_date=START_DATETIME, end_date=END_DATETIME)


usecase_outputs_path = os.path.join(OUTPUTS_PATH, "DEX_trades")
dex_trades_file = f"dex_trades_{START_DATE_STR}_{END_DATE_STR}.csv"
protocols_file = f"protocols_count_{START_DATE_STR}_{END_DATE_STR}.csv"

dex_trades_path = f"{usecase_outputs_path}/{dex_trades_file}"
protocols_path =  f"{usecase_outputs_path}/{protocols_file}"


if os.path.exists(dex_trades_path) and os.path.exists(protocols_path):
    logging.warn(f"Existing data for the time range [{START_DATETIME},{END_DATETIME}]. Reading DEX trades and protocols dataframes from file.")
    protocols_df = pd.read_csv(protocols_path)
    trades_df =  pd.read_csv(dex_trades_path)
else:
    halloween_trades_dict = trades_retriever.transform() # ~45 min running time for 5 days. ~ 15min for 2 days
    protocols_df = halloween_trades_dict["ProtocolDexTrCounts"]
    trades_df = halloween_trades_dict["DEX_Trades"]
                                      
    protocols_df.to_csv(protocols_path, index = False)
    trades_df.to_csv(dex_trades_path, index = False)

2022-11-02 13:35:47 [WARNING] root - Existing data for the time range [2022-10-31 00:00:00,2022-11-01 00:00:00]. Reading DEX trades and protocols dataframes from file.

C:\Users\daniel.salgado\AppData\Local\Temp\ipykernel_22932\836146019.py:2: DeprecationWarning:

The 'warn' function is deprecated, use 'warning' instead


trades_df.shape

(332261, 12)


protocos_df = protocols_df.sort_values(by = "count", ascending = False)


# Top 10 protocols
protocos_df.head(10)


sns.barplot(data=protocos_df.head(10), x = "count", y="protocol");


sold_tokens_counts = trades_df["sold_token_symbol"].value_counts().reset_index(name="counts").rename(columns={"index": "token_symbol"})
sold_tokens_counts.head(15)


sns.barplot(data=sold_tokens_counts.head(15), x = "counts", y="token_symbol");


bought_tokens_counts = trades_df["bought_token_symbol"].value_counts().reset_index(name="counts").rename(columns={"index": "token_symbol"})
bought_tokens_counts.head(15)


sns.barplot(data=bought_tokens_counts.head(15), x = "counts", y="token_symbol");


VITALIK_BUTERIN_ADDRESS = "0xab5801a7d398351b8be11c439e05c5b3259aec9b"

# You can change the values of the three variables below if you want to experiment with different values.
START_DATE_STR = "2022-01-01"
END_DATE_STR = "2022-12-31"

# Do not change code below
START_DATETIME = datetime.strptime(START_DATE_STR, '%Y-%m-%d')
END_DATETIME = datetime.strptime(END_DATE_STR, '%Y-%m-%d')
WALLET_ADDRESSES = [VITALIK_BUTERIN_ADDRESS]

vb_trades_retriever = DEXTradesRetriever(api_key=BITQUERY_API_KEY, start_date=START_DATETIME, end_date=END_DATETIME,
                                      wallet_addresses=WALLET_ADDRESSES)


vb_df_dict = vb_trades_retriever.transform()

2022-11-02 13:35:50 [INFO] root - Retrieving number of DEX transactions per protocol data...
2022-11-02 13:35:51 [INFO] root - Retrieving DEX transactions all available protocols, from 2022-01-01 to 2022-12-31...
2022-11-02 13:35:51 [WARNING] root -         - Waiting for 5.5 seconds until next request to the Bitquery Api due to free plan limits.
2022-11-02 13:35:58 [WARNING] root -         - Waiting for 4.6 seconds until next request to the Bitquery Api due to free plan limits.
2022-11-02 13:36:05 [INFO] root -     Finished in 2 iterations.


vb_df_dict.keys()

dict_keys(['DEX_Trades', 'ProtocolDexTrCounts'])


vb_dex_trades = vb_df_dict["DEX_Trades"]
vb_dex_trades_by_protocol = vb_df_dict["ProtocolDexTrCounts"]


vb_dex_trades.head()


sold_tokens_counts = vb_dex_trades["sold_token_symbol"].value_counts().reset_index(name="counts").rename(columns={"index": "token_symbol"})
sold_tokens_counts.head(10)


sns.barplot(data=sold_tokens_counts.query('counts > 1'), x = "counts", y="token_symbol");


bought_tokens_counts = vb_dex_trades["bought_token_symbol"].value_counts().reset_index(name="counts").rename(columns={"index": "token_symbol"})
bought_tokens_counts.head(10)


sns.barplot(data=bought_tokens_counts.query('counts > 1'), x = "counts", y="token_symbol");


# Code your function below


# Check your function for transactions where the 'To' address is the input address

# <Replace this with your code>


# Check your function for transactions where the 'From' address is the input address

# <Replace this with your code>


# Retrieve transactions and store as pandas dataframe


# Preprocess and prepare the dataframes for visualizations


# Count plot


# Line plot


# Create cells and add code as needed


# Create cells and add code as needed


# Create cells and add code as needed

	id	symbol	name
0	01coin	zoc	01coin
1	0-5x-long-algorand-token	algohalf	0.5X Long Algorand
2	0-5x-long-altcoin-index-token	althalf	0.5X Long Altcoin Index
3	0-5x-long-ascendex-token-token	asdhalf	0.5X Long AscendEx
4	0-5x-long-bitcoin-cash-token	bchhalf	0.5X Long Bitcoin Cash

	datetime	price_in_usd	market_cap_in_usd	24h_volume_in_usd	coin_id
0	2021-01-01	29022.418395	5.394380e+11	4.350352e+10	bitcoin
1	2021-01-02	29352.126792	5.455933e+11	3.408972e+10	bitcoin
2	2021-01-03	32163.824935	5.978877e+11	5.727344e+10	bitcoin
3	2021-01-04	33008.226203	6.136169e+11	1.788941e+11	bitcoin
4	2021-01-05	31515.575967	5.857263e+11	7.465717e+10	bitcoin

	protocol	count
30	Uniswap v2	256214
31	Uniswap v3	88975
4	Balancer v2	4096
33	Zerox Exchange v2	1453
35	Zerox Exchange v4	1387
3	Balancer Pool Token	986
6	Bancor Network v2	858
8	Curve	359
12	Dodo	299
29	Uniswap	197

	token_symbol	counts
0	WETH	125815
1	USDC	28865
2	USDT	12461
3	DAI	3911
4	Vine	3868
5	SHIB	2433
6	WBTC	2419
7	LMEOW	2296
8	USHI	2104
9	BUSHI	1967
10	Error in symbol	1913
11	TITTER	1810
12	TAMA	1702
13	HEX	1553
14	$TRUMP	1540

	token_symbol	counts
0	WETH	163475
1	USDC	28853
2	USDT	13159
3	DAI	4178
4	Vine	2619
5	WBTC	2486
6	SHIB	1969
7	LMEOW	1494
8	HEX	1382
9	$TRUMP	1325
10	Error in symbol	1307
11	DC	1200
12	TITTER	1175
13	TAMA	1161
14	XEN	978

	blockNumber	timeStamp	hash	nonce	blockHash	transactionIndex	from	to	value	gas	gasPrice	txreceipt_status	input	cumulativeGasUsed	gasUsed	confirmations	methodId
0	15834718	1666817063	0x158fbc1f6d8b44409c4adfe0b51bb255f61b36b00a32fa0d1bcd80cc239cf07e	1317	0x79ea7326cd8d180d925e398756880445f060a4eefc9fb972cfda0304c212b492	113	0x1eaf03af983d1e20b1bab73964906c281e141d4f	0xab5801a7d398351b8be11c439e05c5b3259aec9b	0	32868	17364769372	1	0x68747470733a2f2f747769747465722e636f6d2f56425f546f6b656e2f7374617475732f313538353337303332323436363835323836350a0a	15593267	21912	47658	0x68747470
1	15833688	1666804655	0xd0cdc5ee47422c606de85854acdd70a4178145570b907fc9e3d5aaf0af7d2245	1315	0xcd4c6b8f9726649a85dd5bdaff6f2fc6c3774dd01e2bde3a481c5254b4e2e6bb	108	0x1eaf03af983d1e20b1bab73964906c281e141d4f	0xab5801a7d398351b8be11c439e05c5b3259aec9b	0	40572	31584089397	1	0x57652077696c6c2072697365207468652024564220746f6b656e20696e20796f757220686f6e6f75722c2042657374206e61727261746976652073696e636520534849420a0a4f776e65722064756d706564206f6e2074686520636f6d6d756e69747920616e6420636c6f736573207468652074656c656772616d2c62656361757365206f6620746865204c50206265656e2031303025206275726e656420616e642074686520636f6e74726163742072656e6f756e6365642c2074686520636f6d6d756e69747920726f7365206974206261636b2066726f6d2064656174682062656361757365206f66207768617420796f7520676976652075732064617920696e20616e6420646179206f75742077697468207468652045544820626c6f636b636861696e200a0a7468616e6b796f752c20796f75206861766520737570706c7920746f20646f207768617420796f75207769736820776974682e0a0a68747470733a2f2f742e6d652f5642636f6d6d756e697479455243	8676810	27048	48688	0x57652077
2	15828766	1666745363	0x4655ccc7f011a1e6ce378c1e781fd1c26290f655564185f724cc125b41707d86	209	0xaa5a81e87fa41e48fbede4ad5c8b6902c26ca720e2d4ee07107fb0e4b9918b2e	106	0xab5801a7d398351b8be11c439e05c5b3259aec9b	0xc79b96044906550a5652bcf20a6ea02f139b9ae5	3923686000000000000	21000	12383671336	1	0x	14001536	21000	53610	0x

	date	blockNumber	hash	from	to	value	gas	gasUsed	gasPrice
0	2022-10-26 20:44:23	15834718	0x158fbc1f6d8b44409c4adfe0b51bb255f61b36b00a32fa0d1bcd80cc239cf07e	0x1eaf03af983d1e20b1bab73964906c281e141d4f	0xab5801a7d398351b8be11c439e05c5b3259aec9b	0.0	32868.0	21912.0	17364769372.0
1	2022-10-26 17:17:35	15833688	0xd0cdc5ee47422c606de85854acdd70a4178145570b907fc9e3d5aaf0af7d2245	0x1eaf03af983d1e20b1bab73964906c281e141d4f	0xab5801a7d398351b8be11c439e05c5b3259aec9b	0.0	40572.0	27048.0	31584089397.0
2	2022-10-26 00:49:23	15828766	0x4655ccc7f011a1e6ce378c1e781fd1c26290f655564185f724cc125b41707d86	0xab5801a7d398351b8be11c439e05c5b3259aec9b	0xc79b96044906550a5652bcf20a6ea02f139b9ae5	3923686000000000000.0	21000.0	21000.0	12383671336.0

	gasPrice	gasValue	hash	gas	amount	to.address	sender.address	currency.address	currency.name	currency.symbol	block.height	block.timestamp.time
0	17.364769	0.000380	0x158fbc1f6d8b44409c4adfe0b51bb255f61b36b00a32fa0d1bcd80cc239cf07e	21912.0	0.000000	0xab5801a7d398351b8be11c439e05c5b3259aec9b	0x1eaf03af983d1e20b1bab73964906c281e141d4f	-	Ether	ETH	15834718	2022-10-26 20:44:23
1	31.584090	0.000854	0xd0cdc5ee47422c606de85854acdd70a4178145570b907fc9e3d5aaf0af7d2245	27048.0	0.000000	0xab5801a7d398351b8be11c439e05c5b3259aec9b	0x1eaf03af983d1e20b1bab73964906c281e141d4f	-	Ether	ETH	15833688	2022-10-26 17:17:35
2	12.383671	0.000260	0x4655ccc7f011a1e6ce378c1e781fd1c26290f655564185f724cc125b41707d86	21000.0	3.923686	0xc79b96044906550a5652bcf20a6ea02f139b9ae5	0xab5801a7d398351b8be11c439e05c5b3259aec9b	-	Ether	ETH	15828766	2022-10-26 00:49:23

	id	symbol	name
1629	bitcoin	btc	Bitcoin
2256	cardano	ada	Cardano
4214	ethereum	eth	Ethereum
8393	oasis-network	rose	Oasis Network
10351	shiba-inu	shib	Shiba Inu
10676	solana	sol	Solana

	transaction_from_address	datetime	protocol	total_trade_amount_usd	sold_token_symbol	sold_tokens	bought_tokens	bought_token_symbol	transaction_gas_value	bought_token_address	sold_token_address	transaction_hash
0	0x24f8e4f5188f63a882e453d10636a0112fd6c7e1	2022-01-04 14:09:18	Uniswap v2	76962.197266	FOMO	1.482848e+12	20.000	WETH	0.012183	0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2	0xb68f32410a7dd4cf7a1ae18c6b6ddefa2eed80b3	0xdea19cb511080b06ec705105abb168609066d2a29a2a8e5082a6d4d6db758ea8
1	0xf5c0f8388d32ee34deb0e34fa9eff279f54448c4	2022-01-06 21:20:25	Uniswap v2	13721.250977	SHIBA DAO	6.922182e+10	4.000	WETH	0.015196	0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2	0xf1656ae9e8227da5ebb93406e2edd74d9820d0ad	0xe4a50708b27563b031d2f16b2535c6f08e22bb9a7cbec171d2b90a4e5599a9fa
2	0x6081bb069380cd45753b21857b259a0be76f23e7	2022-01-08 06:26:52	Uniswap v2	4826.196533	RON	5.095918e+09	1.500	WETH	0.009534	0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2	0xebfc9d08f47b6b4411eaf5b97f1fd7d9b6d2929c	0xe3e93539f72e1e6695b6fc39b4bdd99e587cc768b24bfd3464ba7e4023105052
3	0x7699833498006a7a8f42835a842e124c8f9a3ae3	2022-01-10 05:05:07	Uniswap v2	5949.725798	WEB3.0	1.253301e+10	1.873	WETH	0.011379	0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2	0xd1bfb50bcb96635cf13fe50ba507ebbf37b09234	0xbc74997dccb810a6819fa9fe3cef44b8f1b73b96cad0d3a5903f7ffe54589d4f
4	0x012c192653fefdf2b4626011ae8cecab40c2ed86	2022-01-12 11:59:53	Uniswap v2	27681.426636	LOVE	6.738590e+10	8.500	WETH	0.014988	0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2	0x2efc83c953f7a6a059744270c358edd6162f89d7	0x4ca1f1184e5150992f463107378c510f22245921ecd76d60d12f56be9a6e4689

	token_symbol	counts
0	SHIB	15
1	GAL	2
2	USDC	2
3	Ape DAO	2
4	UkraineDAO	2
5	$NOTHING	1
6	GOAL	1
7	ROPs	1
8	VYPER	1
9	DETH	1

Overview¶

Tutorial roadmap (~ 3 h)¶

1. Introduction to Blockchain¶

1.1 What is Blockchain¶

1.2 Blockchain and cryptocurrencies¶

1.3 The blockchain ecosystem¶

1.4 Blockchain and the new Web 3.0¶

2. Understanding Blockchain data: the Ethereum Blockchain¶

2.1 The Ethereum blockchain¶

2.1.1 What is Ether¶

2.1.2 What are smart contracts?¶

2.1.3 What is an Ethereum Account? And a (crypto) Wallet?¶

2.1.4 Transactions within the Ethereum Blockchain¶

The Life Cycle of a Transaction (Detailed explanations here)¶

2.1.4 Enterprise Ethereum use cases¶

2.2 Understanding the Ethereum blockchain with Data in the Etherscan explorer¶

2.2.1 Blocks¶

2.2.2 "Address" related pages: Wallets, Smart Contract and Tokens¶

A. Token page¶

B. Smart Contract Address page¶

C. Wallet Address (EOA) page¶

2.2.3 Transactions¶

3. Hands-on: interpretation, retrieval from APIs, processing and visualization of blockchain data with Python¶

How to create an Etherscan API key¶

How to obtain your own Bitquery API key¶

3.1 Basic introduction to API requests¶

3.1.1 Etherscan¶

3.1.2 Bitquery¶

3.1.3 Coingecko¶

3.2 Usecases¶

Usecase I: DEX transactions¶

DEXTradesRetriever¶

Description¶

Class Parameters¶

DEX trades during Halloween 2022 "holidays"¶

Protocol (DEX) popularity for trading¶

Top 15 most sold tokens¶

Top 15 most bought tokens¶

Vitalik Buterin DEX trades in 2022¶

Most sold tokens¶

Most bought tokens¶

3.3 Exercises¶

3.3.1 (Exercises) Basic Introduction to API requests¶

Basic Exercise I: Transactions timeseries for a particular Wallet address and Time range. (Beginner)¶

Basic Exercise II: Historical coin info using Coingecko API without a client (Beginner)¶

3.3.2 (Exercises) Usecases¶

Usecase Exercise I: Last Week Most Traded Tokens in DEX (Intermediate)¶

Usecase Exercise II: Ethereum Biggest Moves (Advanced)¶

4. Additional resources and links¶

Blockchain APIs¶

Links¶

Courses & Tutorials¶

Web-Apps¶

Certifications¶

References¶

5. Exercise solutions¶